﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Project_ScansiaPacific
{
    public partial class FrDGCLGS : DevComponents.DotNetBar.Office2007Form
    {
        public FrDGCLGS()
        {
            InitializeComponent();
        }
        private string MaSo1, W1, TyTrong1, KhoiLuong1, CDay1, CRong1, CDai1, Nguon1, LoSay1, Ngay1, Day1, Rong1, Dai1, Mop1, CongBung1, CongCanh1, Venh1, Mat1, DuongRuot1, Giac1, TetDau1, TetGiua1, GhiChi1, Khac1,SoBB1,UserNhap;
        private int i = 0, j = 0;
        private double d = 0;
        private void rd_TruocSay_CheckedChanged(object sender, EventArgs e)
        {
            lb_NgayVaoRa.Text = "Ngày vào lò";
        }

        private void rd_SauSay_CheckedChanged(object sender, EventArgs e)
        {
            lb_NgayVaoRa.Text = "Ngày ra lò";
        }
        private void comboload()
        {
            string[] arr_NguonGo = new string[] { "Thanh Minh Khánh","Hòa Nga","Đồng Hiệp","Như Ý Ngọc"};
            for (i = 0; i < arr_NguonGo.Length; i++)
            {
                cb_Nguon.Items.Add(arr_NguonGo[i].ToString()); 
            }
            string[] arr_MaSo = new string[] {"1a","1b","1c","2a","2b","2c","3a","3b","3c","4a","4b","4c","5a","5b","5c","6a","6b","6c","7a","7b","7c"};
            for (i = 0; i < arr_MaSo.Length; i++)
            {
                cb_MaSo.Items.Add(arr_MaSo[i].ToString());
            }

        }
        public void checktext_number(TextBox txt)
        {
            try
            {
                if (txt.Text != "")
                {
                    d = double.Parse(txt.Text);
                }
            }
            catch (Exception ex)//Bẫy lỗi khi nhập chữ vào text box
            {
                string s = "Input string";
                if (ex.Message.ToString().Contains(s))
                {
                    MessageBox.Show("Giá trị nhập phải là số");
                    txt.Text = "";
                }
                else
                    MessageBox.Show(ex.Message);
            }
        }

        private void FrDGCLGS_Load(object sender, EventArgs e)
        {
             comboload();
        }

        private void txt_CDay_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_CDay);
        }

        private void txt_CRong_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_CRong);
        }

        private void txt_CDai_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_CDai);
        }

        private void txt_AmDo_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_AmDo);
        }

        private void txt_TyTrong_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_TyTrong);
        }

        private void txt_KhoiLuong_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_KhoiLuong);
        }

        private void txt_Day_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_Day);
        }

        private void txt_Rong_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_Rong);
        }

        private void txt_Dai_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_Dai);
        }

        private void txt_Mop_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_Mop);
        }

        private void txt_CongBung_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_CongBung);
        }

        private void txt_CongCanh_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_CongCanh);
        }

        private void txt_Venh_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_Venh);
        }

        private void txt_Mat_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_Mat);
        }

        private void txt_DuongRuot_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_DuongRuot);
        }

        private void txt_Giac_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_Giac);
        }

        private void txt_Khac1_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_Khac1);
        }

        private void txt_TetDau_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_TetDau);
        }

        private void txt_TetGiua_Leave(object sender, EventArgs e)
        {
            checktext_number(txt_TetGiua);
        }
        private void add_grid()
        {
            Process prs = new Process();
            MaSo1 = cb_MaSo.Text.Trim();
            if (txt_CDay.Text == "")
                CDay1 = "0";
            else
                CDay1 = txt_CDay.Text;
            if (txt_CRong.Text == "")
                CRong1 = "0";
            else
                CRong1 = txt_CRong.Text;
            if (txt_CDai.Text == "")
                CDai1 = "0";
            else
                CDai1 = txt_CDai.Text;
            if (txt_AmDo.Text == "")
                W1 = "0";
            else
                W1 = txt_AmDo.Text;
            if (txt_TyTrong.Text == "")
                TyTrong1 = "0";
            else
                TyTrong1 = txt_TyTrong.Text;
            if (txt_KhoiLuong.Text == "")
                KhoiLuong1 = "0";
            else
                KhoiLuong1 = txt_KhoiLuong.Text;
            if (txt_Day.Text == "")
                Day1 = "0";
            else
                Day1 = txt_Day.Text;
            if (txt_Rong.Text == "")
                Rong1 = "0";
            else
                Rong1 = txt_Rong.Text;
            if (txt_Dai.Text == "")
                Dai1 = "0";
            else
                Dai1 = txt_Dai.Text;
            if (txt_Mop.Text == "")
                Mop1 = "0";
            else
                Mop1 = txt_Mop.Text;
            if (txt_CongBung.Text == "")
                CongBung1 = "0";
            else
                CongBung1 = txt_CongBung.Text;
            if (txt_CongCanh.Text == "")
                CongCanh1 = "0";
            else
                CongCanh1 = txt_CongCanh.Text;
            if (txt_Venh.Text == "")
                Venh1 = "0";
            else
                Venh1 = txt_Venh.Text;
            if (txt_Mat.Text == "")
                Mat1 = "0";
            else
                Mat1 = txt_Mat.Text;
            if (txt_DuongRuot.Text == "")
                DuongRuot1 = "0";
            else
                DuongRuot1 = txt_DuongRuot.Text;
            if (txt_Giac.Text == "")
                Giac1 = "0";
            else
                Giac1 = txt_Giac.Text;
            if (txt_TetDau.Text == "")
                TetDau1 = "0";
            else
                TetDau1 = txt_TetDau.Text;
            if (txt_TetGiua.Text == "")
                TetGiua1 = "0";
            else
                TetGiua1 = txt_TetGiua.Text;
            if (txt_Khac1.Text == "")
                Khac1 = "0";
            else
                Khac1 = txt_Khac1.Text;
            GhiChi1 = txt_GhiChu.Text;
            if (cb_MaSo.Text == "")
                MessageBox.Show("Nhập thiếu mã số");
    
            else
            {
                i = 0;
                while(i <=dataGridViewX1.Rows.Count-2)
                {
                    if (MaSo1 != dataGridViewX1.Rows[i].Cells["MaSo"].Value.ToString())
                        i++;
                    else
                        break;
                }
                if (dataGridViewX1.Rows.Count == 1 || i == dataGridViewX1.Rows.Count - 1)
                {
                    dataGridViewX1.Rows.Add(MaSo1, W1, TyTrong1, KhoiLuong1, Day1, Rong1, Dai1, Mop1, CongBung1, CongCanh1, Venh1, Mat1, DuongRuot1, Giac1, TetDau1, TetGiua1, Khac1, GhiChi1);
                    prs.formatcolordatagrid(dataGridViewX1);
                    prs.reset_group_txt(groupBox3);
                    prs.reset_group_txt(groupBox4);
                    prs.reset_group_txt(groupBox5);
                    prs.reset_group_txt(groupBox6);
                    prs.reset_group_txt(groupBox7);
                    cb_MaSo.Focus();
                }
                else
                    MessageBox.Show("Mã số này đã có trong danh sách phía dưới");
            }
        }

        

        private void dataGridViewX1_DoubleClick(object sender, EventArgs e)
        {
            if (dataGridViewX1.Rows.Count > 1)
            {
                try
                {
                    int r = dataGridViewX1.CurrentCell.RowIndex;
                    //MessageBox.Show(r.ToString());
                   
                    dataGridViewX1.Rows.RemoveAt(r);
                    //MessageBox.Show("aaaa");
                    j = dataGridViewX1.Rows.Count - 1;
                    dataGridViewX1.CurrentCell = dataGridViewX1.Rows[j].Cells["MaSo"];
                }
                catch (Exception ex)
                { MessageBox.Show(ex.Message); }
            }
        }

        private void bt_insert_Click(object sender, EventArgs e)
        {
            add_grid();
        }
        private void save()
        {
            Process prs = new Process();
            SqlConnection Conn = prs.Connect();
            SqlCommand cmd = new SqlCommand();
            Conn.Open();
            cmd.Connection = Conn;
            cmd.CommandText = "Ins_ChungTuSay";
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                if (dataGridViewX1.Rows.Count > 1)
                {
                    string[] temp = txt_Ngay.Text.Split('/');
                    Ngay1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                    temp.Clone();
                    SoBB1 = txt_SoBB.Text.Trim();
                    if (rd_TruocSay.Checked == true&&checkbox_add.Checked==true)
                    {
                        if (txt_CDay.Text == "")
                            CDay1 = "0";
                        else
                            CDay1 = txt_CDay.Text;
                        if (txt_CRong.Text == "")
                            CRong1 = "0";
                        else
                            CRong1 = txt_CRong.Text;
                        if (txt_CDai.Text == "")
                            CDai1 = "0";
                        else
                            CDai1 = txt_CDai.Text;
                        
                        Nguon1 = cb_Nguon.Text.Trim();
                        LoSay1 = txt_Losay.Text;
                       
                        UserNhap = lb_user.Text;
                        cmd.Parameters.AddWithValue("@SoBB", SoBB1);
                        cmd.Parameters.AddWithValue("@Nguon", Nguon1);
                        cmd.Parameters.AddWithValue("@CDay", CDay1);
                        cmd.Parameters.AddWithValue("@CRong", CRong1);
                        cmd.Parameters.AddWithValue("@CDai", CDai1);
                        cmd.Parameters.AddWithValue("@LoSay", LoSay1);
                        cmd.Parameters.AddWithValue("@Users", UserNhap);
                        cmd.Parameters.AddWithValue("@Ngay", Ngay1);
                        cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        
                    }
                    cmd.CommandText = "Ins_GoSay";
                    for (i = dataGridViewX1.Rows.Count - 2; i >= 0; i--)
                    {
                        MaSo1 = dataGridViewX1.Rows[i].Cells["MaSo"].Value.ToString();
                        W1 = dataGridViewX1.Rows[i].Cells["W"].Value.ToString();
                        TyTrong1 = dataGridViewX1.Rows[i].Cells["TyTrong"].Value.ToString();
                        KhoiLuong1 = dataGridViewX1.Rows[i].Cells["KhoiLuong"].Value.ToString();
                        Day1 = dataGridViewX1.Rows[i].Cells["Day"].Value.ToString();
                        Rong1 = dataGridViewX1.Rows[i].Cells["Rong"].Value.ToString();
                        Dai1 = dataGridViewX1.Rows[i].Cells["Dai"].Value.ToString();
                        Mop1 = dataGridViewX1.Rows[i].Cells["Mop"].Value.ToString();
                        CongBung1 = dataGridViewX1.Rows[i].Cells["CongBung"].Value.ToString();
                        CongCanh1 = dataGridViewX1.Rows[i].Cells["CongCanh"].Value.ToString();
                        Venh1 = dataGridViewX1.Rows[i].Cells["Venh"].Value.ToString();
                        Mat1 = dataGridViewX1.Rows[i].Cells["Mat"].Value.ToString();
                        DuongRuot1 = dataGridViewX1.Rows[i].Cells["DuongRuot"].Value.ToString();
                        Giac1 = dataGridViewX1.Rows[i].Cells["Giac"].Value.ToString();
                        TetDau1 = dataGridViewX1.Rows[i].Cells["TetDau"].Value.ToString();
                        TetGiua1 = dataGridViewX1.Rows[i].Cells["TetGiua"].Value.ToString();
                        Khac1 = dataGridViewX1.Rows[i].Cells["Khac"].Value.ToString();
                        GhiChi1 = dataGridViewX1.Rows[i].Cells["GhiChu"].Value.ToString();
                        cmd.Parameters.AddWithValue("@SoBB", SoBB1);
                        cmd.Parameters.AddWithValue("@MaSo", MaSo1);
                        cmd.Parameters.AddWithValue("@AmDo", W1);
                        cmd.Parameters.AddWithValue("@TyTrong", TyTrong1);
                        cmd.Parameters.AddWithValue("@KhoiLuong", KhoiLuong1);
                        cmd.Parameters.AddWithValue("@CDay", Day1);
                        cmd.Parameters.AddWithValue("@CRong", Rong1);
                        cmd.Parameters.AddWithValue("@CDai", Dai1);
                        cmd.Parameters.AddWithValue("@Mop", Mop1);
                        cmd.Parameters.AddWithValue("@CongBung", CongBung1);
                        cmd.Parameters.AddWithValue("@CongCanh", CongCanh1);
                        cmd.Parameters.AddWithValue("@Venh", Venh1);
                        cmd.Parameters.AddWithValue("@Mat", Mat1);
                        cmd.Parameters.AddWithValue("@DuongRuot", DuongRuot1);
                        cmd.Parameters.AddWithValue("@Giac", Giac1);
                        cmd.Parameters.AddWithValue("@TetDau", TetDau1);
                        cmd.Parameters.AddWithValue("@TetGiua", TetGiua1);
                        cmd.Parameters.AddWithValue("@Khac", Khac1);
                        cmd.Parameters.AddWithValue("@GhiChu", GhiChi1);
                        if(rd_TruocSay.Checked==true)
                            cmd.Parameters.AddWithValue("@flag", "0");
                        if (rd_SauSay.Checked == true)
                            cmd.Parameters.AddWithValue("@flag", "1");
                        cmd.Parameters.AddWithValue("@Ngay", Ngay1);
                        cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        dataGridViewX1.Rows.RemoveAt(i);
                    }
                    MessageBox.Show("Dữ liệu được lưu thành công");
                    lb_check.Text = "0";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Đã xảy ra lỗi khi lưu dữ liệu, Bạn có thể xóa dòng bị lỗi sau đó lưu tiếp");
                string s = "insert duplicate key";
                string FK = "FOREIGN KEY constraint";
                string s1 = ex.Message.ToString();
                if (s1.Contains(s))
                { MessageBox.Show("Insert không thành công \n" + "Mã đã tồn tại "); }
                else if (s1.Contains(FK))
                { MessageBox.Show("Nhập sai thông tin trong Combobox"); }
                else
                    MessageBox.Show(ex.Message);
                MessageBox.Show("Đã xảy ra lỗi khi thêm dữ liệu, vui lòng kiểm tra lại");
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void bt_save_Click(object sender, EventArgs e)
        {
            save();
        }

        private void FrDGCLGS_KeyDown(object sender, KeyEventArgs ex)
        {
            if (ex.KeyData == Keys.Enter)
            {
                SendKeys.Send("{TAB}");
            }
            if (ex.KeyCode == Keys.F11)
            {

                bt_save_Click(null, null);
            }
            if (ex.KeyCode == Keys.End)
            {
                bt_insert_Click(null, null);
            }
        }

        private DataSet search()
        {
            DataSet ds = new DataSet();
            Process prs = new Process();
            string[] temp = txt_Ngaybegin.Text.Split('/');
            string Ngaystart = temp[1] + "/" + temp[0] + "/" + temp[2];
            string[] temp1 = txt_Ngayend.Text.Split('/');
            string Ngayend = temp1[1] + "/" + temp1[0] + "/" + temp1[2];
            string sql = null;
            if (txt_SoBBsearch.Text == "")
            {
                sql = @"select qry1.*,qry2.* from
                        (SELECT     dbo.LoSay_ChungTuSay.SoBB,dbo.LoSay_ChungTuSay.SoBB+dbo.GoSay.MaSo as IDlink, dbo.LoSay_ChungTuSay.Nguon, dbo.LoSay_ChungTuSay.CDay, dbo.LoSay_ChungTuSay.CRong, dbo.LoSay_ChungTuSay.CDai, 
                                                dbo.LoSay_ChungTuSay.LoSay, dbo.GoSay.MaSo, dbo.GoSay.AmDo, dbo.GoSay.TyTrong, dbo.GoSay.KhoiLuong, dbo.GoSay.CDay AS CDay1, 
                                                dbo.GoSay.CRong AS CRong1, dbo.GoSay.CDai AS CDai1, dbo.GoSay.Mop, dbo.GoSay.CongBung, dbo.GoSay.CongCanh, dbo.GoSay.Venh, dbo.GoSay.Mat, 
                                                dbo.GoSay.DuongRuot, dbo.GoSay.Giac, dbo.GoSay.TetDau, dbo.GoSay.TetGiua, dbo.GoSay.Khac, dbo.GoSay.GhiChu, dbo.GoSay.Ngay
                        FROM         dbo.LoSay_ChungTuSay INNER JOIN
                                                dbo.GoSay ON dbo.LoSay_ChungTuSay.SoBB = dbo.GoSay.SoBB
                                                where GoSay.Ngay>='" + Ngaystart + " 00:00:00' and GoSay.Ngay<='" + Ngayend + " 23:59:59' and GoSay.flag=0) as qry1 left join";
                sql += @"        (SELECT     dbo.GoSay.SoBB,GoSay.SoBB+dbo.GoSay.MaSo as IDlink,dbo.GoSay.MaSo, dbo.GoSay.AmDo, dbo.GoSay.TyTrong, dbo.GoSay.KhoiLuong, dbo.GoSay.CDay AS CDay1, 
                                                dbo.GoSay.CRong AS CRong1, dbo.GoSay.CDai AS CDai1, dbo.GoSay.Mop, dbo.GoSay.CongBung, dbo.GoSay.CongCanh, dbo.GoSay.Venh, dbo.GoSay.Mat, 
                                                dbo.GoSay.DuongRuot, dbo.GoSay.Giac, dbo.GoSay.TetDau, dbo.GoSay.TetGiua, dbo.GoSay.Khac, dbo.GoSay.GhiChu, dbo.GoSay.Ngay
                        FROM         dbo.GoSay where GoSay.flag=1) as qry2 on qry1.IDlink=qry2.IDlink       ";
            }
            else
            {
                sql = @"select qry1.*,qry2.* from
                        (SELECT     dbo.LoSay_ChungTuSay.SoBB,dbo.LoSay_ChungTuSay.SoBB+dbo.GoSay.MaSo as IDlink, dbo.LoSay_ChungTuSay.Nguon, dbo.LoSay_ChungTuSay.CDay, dbo.LoSay_ChungTuSay.CRong, dbo.LoSay_ChungTuSay.CDai, 
                                                dbo.LoSay_ChungTuSay.LoSay, dbo.GoSay.MaSo, dbo.GoSay.AmDo, dbo.GoSay.TyTrong, dbo.GoSay.KhoiLuong, dbo.GoSay.CDay AS CDay1, 
                                                dbo.GoSay.CRong AS CRong1, dbo.GoSay.CDai AS CDai1, dbo.GoSay.Mop, dbo.GoSay.CongBung, dbo.GoSay.CongCanh, dbo.GoSay.Venh, dbo.GoSay.Mat, 
                                                dbo.GoSay.DuongRuot, dbo.GoSay.Giac, dbo.GoSay.TetDau, dbo.GoSay.TetGiua, dbo.GoSay.Khac, dbo.GoSay.GhiChu, dbo.GoSay.Ngay
                        FROM         dbo.LoSay_ChungTuSay INNER JOIN
                                                dbo.GoSay ON dbo.LoSay_ChungTuSay.SoBB = dbo.GoSay.SoBB
                                                where GoSay.SoBB='"+txt_SoBBsearch.Text+"' and GoSay.flag=0) as qry1 left join";
                sql += @"        (SELECT     dbo.GoSay.SoBB,GoSay.SoBB+dbo.GoSay.MaSo as IDlink,dbo.GoSay.MaSo, dbo.GoSay.AmDo, dbo.GoSay.TyTrong, dbo.GoSay.KhoiLuong, dbo.GoSay.CDay AS CDay1, 
                                                dbo.GoSay.CRong AS CRong1, dbo.GoSay.CDai AS CDai1, dbo.GoSay.Mop, dbo.GoSay.CongBung, dbo.GoSay.CongCanh, dbo.GoSay.Venh, dbo.GoSay.Mat, 
                                                dbo.GoSay.DuongRuot, dbo.GoSay.Giac, dbo.GoSay.TetDau, dbo.GoSay.TetGiua, dbo.GoSay.Khac, dbo.GoSay.GhiChu, dbo.GoSay.Ngay
                        FROM         dbo.GoSay where GoSay.flag=1) as qry2 on qry1.IDlink=qry2.IDlink       ";
            }
            
              ds = prs.dts(sql);
              return ds;
          

        }

        private void bt_search_Click(object sender, EventArgs e)
        {
            try
            {
                
                DataSet ds = search();
                
                dataGridViewX2.DataSource = ds.Tables[0];
                if (ds.Tables[0].Rows.Count == 0) 
                    MessageBox.Show("Không có dữ liệu");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void search_xoa()
        {
            DataSet ds = new DataSet();
            Process prs = new Process();
            string[] temp = txt_Ngaybegin_Xoa.Text.Split('/');
            string Ngaystart = temp[1] + "/" + temp[0] + "/" + temp[2];
            string[] temp1 = txt_Ngayend_Xoa.Text.Split('/');
            string Ngayend = temp1[1] + "/" + temp1[0] + "/" + temp1[2];
            string sql = null;
            if (rd_TruocSaysearch.Checked==true)
            {
                if (txt_SoBBXoa.Text == "")
                {
                    sql = @"SELECT     GoSay.Serial, dbo.LoSay_ChungTuSay.SoBB,dbo.LoSay_ChungTuSay.SoBB+dbo.GoSay.MaSo as IDlink, dbo.LoSay_ChungTuSay.Nguon, dbo.LoSay_ChungTuSay.CDay, dbo.LoSay_ChungTuSay.CRong, dbo.LoSay_ChungTuSay.CDai, 
                      dbo.LoSay_ChungTuSay.LoSay, dbo.GoSay.MaSo, dbo.GoSay.AmDo, dbo.GoSay.TyTrong, dbo.GoSay.KhoiLuong, dbo.GoSay.CDay AS CDay1, 
                      dbo.GoSay.CRong AS CRong1, dbo.GoSay.CDai AS CDai1, dbo.GoSay.Mop, dbo.GoSay.CongBung, dbo.GoSay.CongCanh, dbo.GoSay.Venh, dbo.GoSay.Mat, 
                      dbo.GoSay.DuongRuot, dbo.GoSay.Giac, dbo.GoSay.TetDau, dbo.GoSay.TetGiua, dbo.GoSay.Khac, dbo.GoSay.GhiChu, dbo.GoSay.Ngay
                        FROM         dbo.LoSay_ChungTuSay INNER JOIN
                      dbo.GoSay ON dbo.LoSay_ChungTuSay.SoBB = dbo.GoSay.SoBB
                      where GoSay.Ngay>='" + Ngaystart + " 00:00:00' and GoSay.Ngay<='" + Ngayend + " 23:59:59' and GoSay.flag=0";
                }
                else
                {
                    sql = @"SELECT     GoSay.Serial,dbo.LoSay_ChungTuSay.SoBB,dbo.LoSay_ChungTuSay.SoBB+dbo.GoSay.MaSo as IDlink, dbo.LoSay_ChungTuSay.Nguon, dbo.LoSay_ChungTuSay.CDay, dbo.LoSay_ChungTuSay.CRong, dbo.LoSay_ChungTuSay.CDai, 
                      dbo.LoSay_ChungTuSay.LoSay, dbo.GoSay.MaSo, dbo.GoSay.AmDo, dbo.GoSay.TyTrong, dbo.GoSay.KhoiLuong, dbo.GoSay.CDay AS CDay1, 
                      dbo.GoSay.CRong AS CRong1, dbo.GoSay.CDai AS CDai1, dbo.GoSay.Mop, dbo.GoSay.CongBung, dbo.GoSay.CongCanh, dbo.GoSay.Venh, dbo.GoSay.Mat, 
                      dbo.GoSay.DuongRuot, dbo.GoSay.Giac, dbo.GoSay.TetDau, dbo.GoSay.TetGiua, dbo.GoSay.Khac, dbo.GoSay.GhiChu, dbo.GoSay.Ngay
                        FROM         dbo.LoSay_ChungTuSay INNER JOIN
                      dbo.GoSay ON dbo.LoSay_ChungTuSay.SoBB = dbo.GoSay.SoBB
                      where GoSay.SoBB='"+txt_SoBBXoa.Text+"' and GoSay.flag=0";
                }
            }
            if(rd_SauSaysearch.Checked==true)
            {
                if (txt_SoBBXoa.Text == "")
                {
                    sql = @"SELECT      GoSay.Serial,dbo.LoSay_ChungTuSay.SoBB,dbo.LoSay_ChungTuSay.SoBB+dbo.GoSay.MaSo as IDlink, dbo.LoSay_ChungTuSay.Nguon, dbo.LoSay_ChungTuSay.CDay, dbo.LoSay_ChungTuSay.CRong, dbo.LoSay_ChungTuSay.CDai, 
                      dbo.LoSay_ChungTuSay.LoSay, dbo.GoSay.MaSo, dbo.GoSay.AmDo, dbo.GoSay.TyTrong, dbo.GoSay.KhoiLuong, dbo.GoSay.CDay AS CDay1, 
                      dbo.GoSay.CRong AS CRong1, dbo.GoSay.CDai AS CDai1, dbo.GoSay.Mop, dbo.GoSay.CongBung, dbo.GoSay.CongCanh, dbo.GoSay.Venh, dbo.GoSay.Mat, 
                      dbo.GoSay.DuongRuot, dbo.GoSay.Giac, dbo.GoSay.TetDau, dbo.GoSay.TetGiua, dbo.GoSay.Khac, dbo.GoSay.GhiChu, dbo.GoSay.Ngay
                        FROM         dbo.LoSay_ChungTuSay INNER JOIN
                      dbo.GoSay ON dbo.LoSay_ChungTuSay.SoBB = dbo.GoSay.SoBB
                      where GoSay.Ngay>='" + Ngaystart + " 00:00:00' and GoSay.Ngay<='" + Ngayend + " 23:59:59' and GoSay.flag=1";
                }
                else
                {
                    sql = @"SELECT      GoSay.Serial,dbo.LoSay_ChungTuSay.SoBB,dbo.LoSay_ChungTuSay.SoBB+dbo.GoSay.MaSo as IDlink, dbo.LoSay_ChungTuSay.Nguon, dbo.LoSay_ChungTuSay.CDay, dbo.LoSay_ChungTuSay.CRong, dbo.LoSay_ChungTuSay.CDai, 
                      dbo.LoSay_ChungTuSay.LoSay, dbo.GoSay.MaSo, dbo.GoSay.AmDo, dbo.GoSay.TyTrong, dbo.GoSay.KhoiLuong, dbo.GoSay.CDay AS CDay1, 
                      dbo.GoSay.CRong AS CRong1, dbo.GoSay.CDai AS CDai1, dbo.GoSay.Mop, dbo.GoSay.CongBung, dbo.GoSay.CongCanh, dbo.GoSay.Venh, dbo.GoSay.Mat, 
                      dbo.GoSay.DuongRuot, dbo.GoSay.Giac, dbo.GoSay.TetDau, dbo.GoSay.TetGiua, dbo.GoSay.Khac, dbo.GoSay.GhiChu, dbo.GoSay.Ngay
                        FROM         dbo.LoSay_ChungTuSay INNER JOIN
                      dbo.GoSay ON dbo.LoSay_ChungTuSay.SoBB = dbo.GoSay.SoBB
                      where GoSay.SoBB='" + txt_SoBBXoa.Text + "' and GoSay.flag=1";
                }
            }
            try
            {

                ds = prs.dts(sql);
                
                    dataGridViewX3.DataSource = ds.Tables[0];
                    if (ds.Tables[0].Rows.Count == 0)
                        MessageBox.Show("Không có dữ liệu");
               
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void delete()
        {
            Process prs = new Process();
            SqlCommand cmd = new SqlCommand();
            SqlConnection Conn = prs.Connect();
            
            Conn.Open();
            cmd.Connection = Conn;

            try
            {
                j = dataGridViewX3.CurrentCell.RowIndex;
               
                GhiChi1 = dataGridViewX3.Rows[j].Cells["Serial"].Value.ToString();
               
                DialogResult d = MessageBox.Show("Bạn có chắc muốn xóa Thông Tin có Số Serial " + GhiChi1, "Thông Báo", MessageBoxButtons.OKCancel);
                if (d == DialogResult.OK)
                {
                    cmd.CommandText = @"DELETE FROM [dbo].[GoSay]
                    WHERE Serial="+GhiChi1;
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Dữ liệu được xóa thành công");
                    for (i = dataGridViewX3.Rows.Count - 2; i >= 0; i--)
                    {
                        dataGridViewX3.Rows.RemoveAt(i);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Đã xảy ra lỗi. Dữ liệu xóa KHÔNG thành công");
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void bt_search_xoa_Click(object sender, EventArgs e)
        {
            search_xoa();
        }

        private void bt_delete_Click(object sender, EventArgs e)
        {
           delete();
        }
        private void exportexcel()
        {
            Process prs = new Process();
            DataSet ds = search();
            try
            {

                prs.ExportExcel(ds.Tables[0], "C:\\Huynh\\DanhGiaCLSayGo.xlsx", 6, 1);
                ds.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void bt_exportexcel_Click(object sender, EventArgs e)
        {
            exportexcel();
        }

        public void Users(string Users)
        {
            lb_user.Text = Users;
        }

       
    }
}